﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace TopApp.Library
{
    public class AppDataBase
    {
        private Database DB;
        private String DBName;

        public AppDataBase()
        {
            AppDBConfig config = new AppDBConfig();

            this.DBName = config.name;
            config.name = "";

            this.DB = new Database(config);
            this.initTableStructure();
        }

        private void initTableStructure()
        {
            this.DB.resetComm();

            // 检查数据库是否已经存在

            this.DB.comm.CommandText = "if db_id('" + this.DBName + "') is null CREATE DATABASE " + this.DBName;
            this.DB.comm.ExecuteNonQuery();

            this.DB.comm.CommandText = "use " + this.DBName;
            this.DB.comm.ExecuteNonQuery();

            this.DB.comm.CommandText = "IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fail_trade]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
                "CREATE TABLE [dbo].[fail_trade] ( "+
                "[id] [int] IDENTITY (1, 1) NOT NULL ,"+
                "[tid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,"+
                "[fail_time] [datetime] NOT NULL ,"+
                "[text] [text] COLLATE Chinese_PRC_CI_AS NULL " +
                ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
            this.DB.comm.ExecuteNonQuery();

            this.DB.comm.CommandText = "IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fail_inventory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
                "CREATE TABLE [dbo].[fail_inventory] ( "+
	            "[id] [int] IDENTITY (1, 1) NOT NULL , "+
	            "[item_number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , "+
	            "[text] [text] COLLATE Chinese_PRC_CI_AS NOT NULL "+
                ") ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]";
            this.DB.comm.ExecuteNonQuery();

            this.DB.comm.CommandText = "IF NOT EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[fail_item]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) " +
                "CREATE TABLE [dbo].[fail_item] (" +
                "[id] [int] IDENTITY (1, 1) NOT NULL ," +
                "[taobao_iid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ," +
                "[taobao_outerid] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL" +
                ") ON [PRIMARY]";
            this.DB.comm.ExecuteNonQuery();
        }

        public Boolean insertFailTrade( long Tid, string text )
        {
            this.DB.resetComm();


            this.DB.comm.CommandText = "DELETE FROM fail_trade WHERE tid=@tid";

            this.DB.comm.Parameters.Add("@tid", System.Data.SqlDbType.VarChar);
            this.DB.comm.Parameters.Add("@fail_time", System.Data.SqlDbType.VarChar);
            this.DB.comm.Parameters.Add("@text", System.Data.SqlDbType.VarChar);

            this.DB.comm.Parameters["@tid"].Value = Tid.ToString();
            this.DB.comm.Parameters["@fail_time"].Value = DateTime.Now.ToString();
            this.DB.comm.Parameters["@text"].Value = text;

            

            try
            {
                this.DB.comm.ExecuteNonQuery();

                this.DB.comm.CommandText = "INSERT INTO fail_trade (tid,fail_time,text) VALUES (@tid,@fail_time,@text)";
                this.DB.comm.ExecuteNonQuery();

                return true;
            }
            catch
            {
                return false;
            }
        }

        public Boolean deleteFailTrade(long Tid)
        {
            this.DB.resetComm();

            this.DB.comm.CommandText = "DELETE FROM fail_trade WHERE tid=@tid";
            this.DB.comm.Parameters.Add("@tid", System.Data.SqlDbType.VarChar);
            this.DB.comm.Parameters["@tid"].Value = Tid.ToString();

            this.DB.comm.ExecuteNonQuery();

            return true;
        }

        public Boolean insertFailInventory(string item_number, string text)
        {
            this.DB.resetComm();

            this.DB.comm.CommandText = "DELETE FROM fail_inventory WHERE item_number=@item_number";

            this.DB.comm.Parameters.Add("@item_number", System.Data.SqlDbType.VarChar);
            this.DB.comm.Parameters.Add("@text", System.Data.SqlDbType.VarChar);

            this.DB.comm.Parameters["@item_number"].Value = item_number;
            this.DB.comm.Parameters["@text"].Value = text;



            try
            {
                this.DB.comm.ExecuteNonQuery();

                this.DB.comm.CommandText = "INSERT INTO fail_inventory (item_number,text) VALUES (@item_number,@text)";
                this.DB.comm.ExecuteNonQuery();

                return true;
            }
            catch
            {
                return false;
            }
        }

        public Boolean insertFailItem(string taobao_iid, string taobao_outerid)
        {
            this.DB.resetComm();


            this.DB.comm.CommandText = "DELETE FROM fail_item WHERE taobao_iid=@taobao_iid";

            this.DB.comm.Parameters.Add("@taobao_iid", System.Data.SqlDbType.VarChar);
            this.DB.comm.Parameters.Add("@taobao_outerid", System.Data.SqlDbType.VarChar);

            this.DB.comm.Parameters["@taobao_iid"].Value = taobao_iid;
            this.DB.comm.Parameters["@taobao_outerid"].Value = taobao_outerid;



            try
            {
                this.DB.comm.ExecuteNonQuery();

                this.DB.comm.CommandText = "INSERT INTO fail_item (taobao_iid,taobao_outerid) VALUES (@taobao_outerid,@taobao_outerid)";
                this.DB.comm.ExecuteNonQuery();

                return true;
            }
            catch
            {
                return false;
            }
        }

        // 获取没保存成功的订单单号
        public List<long> getFailTradeIds()
        {
            List<long> ids = new List<long>();

            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT tid FROM fail_trade";

            SqlDataReader rd = this.DB.comm.ExecuteReader();

            if (rd.HasRows)
            {
                while (rd.Read())
                {
                    ids.Add(Convert.ToInt64(rd["tid"]));
                }
            }

            return ids;
        }

        public DataTable getFailTrades()
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT tid,fail_time,text FROM fail_trade";

            SqlDataAdapter apt = new SqlDataAdapter(this.DB.comm);

            DataTable dt = new DataTable();

            apt.Fill(dt);

            return dt;
        }

        public int getFailTradeCount()
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "SELECT count(tid) FROM fail_trade";

            return Convert.ToInt32(this.DB.comm.ExecuteScalar());
        }

        public int deleteFailTrades()
        {
            this.DB.resetComm();
            this.DB.comm.CommandText = "DELETE FROM fail_trade";

            return Convert.ToInt32(this.DB.comm.ExecuteNonQuery());
        }

    }
}
